import os
from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify, send_file
import pymysql
from functools import wraps
from datetime import datetime, timedelta
import csv
from io import StringIO
import io

base_dir = os.path.dirname(os.path.abspath(__file__))

# 创建 Flask 应用并指定模板目录
app = Flask(__name__, 
            template_folder=os.path.join(base_dir, '../all/templates'),
            static_folder=os.path.join(base_dir, '../frontend/static'))

# 调试信息
print(f"Flask 模板目录: {app.template_folder}")
print(f"静态文件目录: {app.static_folder}")
print(f"模板目录内容: {os.listdir(app.template_folder)}")

app.secret_key = 'your_very_secure_secret_key_here'  # 用于session加密

# MySQL配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'bank2.0'
}

# 登录验证装饰器
def login_required(role=None):
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if 'logged_in' not in session:
                flash('请先登录系统', 'danger')
                return redirect(url_for('login_portal'))
                
            if role and session.get('role') != role:
                flash('您没有权限访问此页面', 'danger')
                # 重定向到用户对应的门户
                if session.get('role') == 'admin':
                    return redirect(url_for('admin_portal'))
                elif session.get('role') == 'customer':
                    return redirect(url_for('customer_portal'))
                return redirect(url_for('login'))
                
            return f(*args, **kwargs)
        return decorated_function
    return decorator

# 首页（根据登录状态重定向）
@app.route('/')
def index():
    if 'logged_in' in session:
        if session.get('role') == 'admin':
            return redirect(url_for('admin_portal'))
        elif session.get('role') == 'customer':
            return redirect(url_for('customer_portal'))
    return redirect(url_for('login'))

# 登录页面
@app.route('/login', methods=['GET', 'POST'])
def login():
    # 如果已经登录，重定向到对应门户
    if 'logged_in' in session:
        if session.get('role') == 'admin':
            return redirect(url_for('admin_portal'))
        elif session.get('role') == 'customer':
            return redirect(url_for('customer_portal'))
    
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        role = request.form['role']
        
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                if role == 'admin':
                    # 验证管理员账号
                    cursor.execute("SELECT * FROM adminInfo WHERE username = %s AND password = %s", 
                                 (username, password))
                    admin = cursor.fetchone()
                    
                    if admin:
                        session['logged_in'] = True
                        session['user_id'] = admin[0]
                        session['username'] = admin[1]
                        session['fullname'] = admin[3]
                        session['role'] = 'admin'
                        session['login_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        flash(f'管理员 {admin[3]}，欢迎回来！', 'success')
                        return redirect(url_for('admin_portal'))
                
                elif role == 'customer':
                    # 验证客户账号（银行卡号和密码）
                    card_id = username  # 这里用户名输入框用于输入卡号
                    cursor.execute("""
                        SELECT cardInfo.cardID, cardInfo.pass, userInfo.customerID, 
                               userInfo.customerName, cardInfo.balance
                        FROM cardInfo
                        JOIN userInfo ON cardInfo.customerID = userInfo.customerID
                        WHERE cardID = %s AND pass = %s
                    """, (card_id, password))
                    customer = cursor.fetchone()
                    
                    if customer:
                        session['logged_in'] = True
                        session['card_id'] = customer[0]
                        session['customer_id'] = customer[2]
                        session['customer_name'] = customer[3]
                        session['balance'] = float(customer[4])
                        session['role'] = 'customer'
                        session['login_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        flash(f'欢迎您，{customer[3]}！', 'success')
                        return redirect(url_for('customer_portal'))
                
                # 登录失败
                flash('用户名或密码错误', 'danger')
        except Exception as e:
            flash(f'登录时发生错误: {str(e)}', 'danger')
        finally:
            conn.close()
    
    return render_template('login.html')

# 登出功能
@app.route('/logout')
def logout():
    username = session.get('username') or session.get('customer_name')
    session.clear()
    flash(f'{username}，您已成功登出系统', 'info')
    return redirect(url_for('login'))

# 管理员门户
@app.route('/admin')
@login_required(role='admin')
def admin_portal():
    # 获取当前日期和时间
    current_date = datetime.now().strftime("%Y年%m月%d日 %A")
    return render_template('admin.html', 
                           username=session.get('username'),
                           fullname=session.get('fullname'),
                           login_time=session.get('login_time'),
                           current_date=current_date)

# 客户门户
@app.route('/customer')
@login_required(role='customer')
def customer_portal():
    # 获取当前日期和时间
    current_date = datetime.now().strftime("%Y年%m月%d日 %A")
    # 确保 balance 是实数类型
    balance = float(session.get('balance')) if session.get('balance') else 0.0
    return render_template('customer.html', 
                           customer_name=session.get('customer_name'),
                           card_id=session.get('card_id'),
                           balance=balance,
                           login_time=session.get('login_time'),
                           current_date=current_date)

# 客户存款功能
@app.route('/customer/deposit', methods=['POST'])
@login_required(role='customer')
def deposit():
    try:
        amount = float(request.json.get('amount'))
        card_id = session.get('card_id')
        
        if amount <= 0:
            return jsonify({'error': '存款金额必须大于0'}), 400
            
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                # 只插入交易记录，触发器会更新余额
                cursor.execute("""
                    INSERT INTO tradeinfo (tradeDate, tradeType, tradeMoney, cardID, remark) 
                    VALUES (NOW(), '存款', %s, %s, 'ATM存款')
                """, (amount, card_id))
                conn.commit()
                
                # 更新session中的余额
                cursor.execute("SELECT balance FROM cardinfo WHERE cardID = %s", (card_id,))
                new_balance = cursor.fetchone()[0]
                session['balance'] = float(new_balance)  # 确保 balance 是浮点数
                
                return jsonify({
                    'status': 'success',
                    'new_balance': new_balance,
                    'message': f'成功存款 ¥{amount:.2f}'
                })
        finally:
            conn.close()
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 客户取款功能
@app.route('/customer/withdraw', methods=['POST'])
@login_required(role='customer')
def withdraw():
    try:
        amount = float(request.json.get('amount'))
        card_id = session.get('card_id')
        current_balance = session.get('balance')
        
        if amount <= 0:
            return jsonify({'error': '取款金额必须大于0'}), 400
            
        if current_balance - amount < 1:
            return jsonify({'error': '账户余额不足，取款后余额不能低于1元'}), 400
            
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                # 只插入交易记录，触发器会更新余额
                cursor.execute("""
                    INSERT INTO tradeinfo (tradeDate, tradeType, tradeMoney, cardID, remark) 
                    VALUES (NOW(), '取款', %s, %s, 'ATM取款')
                """, (amount, card_id))
                conn.commit()
                
                # 更新session中的余额
                cursor.execute("SELECT balance FROM cardinfo WHERE cardID = %s", (card_id,))
                new_balance = cursor.fetchone()[0]
                session['balance'] = float(new_balance)  # 确保 balance 是浮点数
                
                return jsonify({
                    'status': 'success',
                    'new_balance': new_balance,
                    'message': f'成功取款 ¥{amount:.2f}'
                })
        finally:
            conn.close()
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 客户转账功能
@app.route('/customer/transfer', methods=['POST'])
@login_required(role='customer')
def customer_transfer():
    data = request.get_json()
    to_card = data.get('to_card')
    amount = data.get('amount')
    remark = data.get('remark')

    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            try:
                cursor.callproc('transfer_money', (session['card_id'], to_card, amount, remark))
                conn.commit()
                # 获取最新余额
                cursor.execute("SELECT balance FROM cardInfo WHERE cardID = %s", (session['card_id'],))
                new_balance = cursor.fetchone()[0]
                
                # 更新session中的余额
                session['balance'] = new_balance
                
                return jsonify({'status': 'success', 'new_balance': new_balance})
            except pymysql.Error as e:
                error_message = str(e)
                return jsonify({'status': 'error', 'error': error_message})
    finally:
        conn.close()


# 获取客户交易记录
@app.route('/customer/transactions')
@login_required(role='customer')
def get_transactions():
    try:
        card_id = session.get('card_id')
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT tradeDate, tradeType, tradeMoney, remark 
                    FROM tradeInfo 
                    WHERE cardID = %s 
                    ORDER BY tradeDate DESC 
                    LIMIT 10
                """, (card_id,))
                transactions = cursor.fetchall()
                
                # 格式化交易记录
                formatted = []
                for t in transactions:
                    formatted.append({
                        'date': t[0].strftime("%Y-%m-%d %H:%M"),
                        'type': t[1],
                        'amount': float(t[2]),
                        'remark': t[3]
                    })
                
                return jsonify({
                    'status': 'success',
                    'transactions': formatted
                })
        finally:
            conn.close()
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# 假设的银行卡挂失路由
@app.route('/card_loss/<card_id>', methods=['POST'])
def card_loss(card_id):
    if 'logged_in' not in session or session.get('role') != 'admin':
        return redirect(url_for('login'))
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 更新银行卡状态为挂失
            cursor.execute("UPDATE cardInfo SET IsReportLoss = TRUE WHERE cardID = %s", (card_id,))
            conn.commit()
            flash('银行卡挂失成功', 'success')
    except Exception as e:
        flash(f'挂失银行卡时发生错误: {str(e)}', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('manage_cards'))


# ====================== 客户管理 ======================
@app.route('/admin/customers')
@login_required(role='admin')
def manage_customers():
    """客户管理主页面"""
    search_term = request.args.get('search', '')
    search_type = request.args.get('type', 'name')  # name, id, phone
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 基础查询
            query = """
                SELECT customerID, customerName, PID, telephone, address 
                FROM userInfo
            """
            
            # 添加搜索条件
            params = []
            if search_term:
                if search_type == 'name':
                    query += " WHERE customerName LIKE %s"
                    params.append(f"%{search_term}%")
                elif search_type == 'id':
                    query += " WHERE customerID = %s"
                    params.append(int(search_term))
                elif search_type == 'phone':
                    query += " WHERE telephone LIKE %s"
                    params.append(f"%{search_term}%")
            
            query += " ORDER BY customerID DESC"
            cursor.execute(query, params)
            customers = cursor.fetchall()
            
            # 格式化结果
            customer_list = []
            for c in customers:
                customer_list.append({
                    'id': c[0],
                    'name': c[1],
                    'pid': c[2],
                    'phone': c[3],
                    'address': c[4]
                })
                
            return render_template('customer_management.html', 
                                  customers=customer_list,
                                  search_term=search_term,
                                  search_type=search_type)
    finally:
        conn.close()

@app.route('/admin/customers/add', methods=['GET', 'POST'])
@login_required(role='admin')
def add_customer():
    """添加新客户"""
    if request.method == 'POST':
        name = request.form['name']
        pid = request.form['pid']
        phone = request.form['phone']
        address = request.form['address']
        
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                cursor.execute(
                    "INSERT INTO userInfo (customerName, PID, telephone, address) "
                    "VALUES (%s, %s, %s, %s)",
                    (name, pid, phone, address)
                )
                conn.commit()
                flash('客户添加成功', 'success')
                return redirect(url_for('manage_customers'))
        except Exception as e:
            conn.rollback()
            flash(f'添加客户失败: {str(e)}', 'danger')
        finally:
            conn.close()
    
    return render_template('add_customer.html')

@app.route('/admin/customers/edit/<int:customer_id>', methods=['GET', 'POST'])
@login_required(role='admin')
def edit_customer(customer_id):
    """编辑客户信息"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                name = request.form['name']
                pid = request.form['pid']
                phone = request.form['phone']
                address = request.form['address']
                
                cursor.execute(
                    "UPDATE userInfo SET customerName = %s, PID = %s, "
                    "telephone = %s, address = %s WHERE customerID = %s",
                    (name, pid, phone, address, customer_id)
                )
                conn.commit()
                flash('客户信息更新成功', 'success')
                return redirect(url_for('manage_customers'))
            
            # 获取当前客户信息
            cursor.execute(
                "SELECT customerName, PID, telephone, address "
                "FROM userInfo WHERE customerID = %s",
                (customer_id,)
            )
            customer = cursor.fetchone()
            
            if not customer:
                flash('客户不存在', 'danger')
                return redirect(url_for('manage_customers'))
                
            return render_template('edit_customer.html', 
                                  customer={
                                      'id': customer_id,
                                      'name': customer[0],
                                      'pid': customer[1],
                                      'phone': customer[2],
                                      'address': customer[3]
                                  })
    finally:
        conn.close()

@app.route('/admin/customers/delete/<int:customer_id>', methods=['POST'])
@login_required(role='admin')
def delete_customer(customer_id):
    """删除客户"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 检查是否有关联的银行卡
            cursor.execute(
                "SELECT COUNT(*) FROM cardInfo WHERE customerID = %s",
                (customer_id,)
            )
            if cursor.fetchone()[0] > 0:
                flash('无法删除客户，该客户有关联的银行卡', 'danger')
                return redirect(url_for('manage_customers'))
                
            cursor.execute(
                "DELETE FROM userInfo WHERE customerID = %s",
                (customer_id,)
            )
            conn.commit()
            flash('客户删除成功', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'删除客户失败: {str(e)}', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('manage_customers'))


# ====================== 银行卡管理 ======================
@app.route('/admin/cards')
@login_required(role='admin')
def manage_cards():
    """银行卡管理主页面"""
    search_term = request.args.get('search', '')
    search_type = request.args.get('type', 'card_id')  # card_id, customer_id, customer_name
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 基础查询
            query = """
                SELECT c.cardID, c.curID, c.savingID, c.openDate, 
                       c.openMoney, c.balance, c.pass, c.ISReportLoss, 
                       c.customerID, u.customerName
                FROM cardInfo c
                JOIN userInfo u ON c.customerID = u.customerID
            """
            
            # 添加搜索条件
            params = []
            if search_term:
                if search_type == 'card_id':
                    query += " WHERE c.cardID LIKE %s"
                    params.append(f"%{search_term}%")
                elif search_type == 'customer_id':
                    query += " WHERE c.customerID = %s"
                    params.append(int(search_term))
                elif search_type == 'customer_name':
                    query += " WHERE u.customerName LIKE %s"
                    params.append(f"%{search_term}%")
            
            query += " ORDER BY c.openDate DESC"
            cursor.execute(query, params)
            cards = cursor.fetchall()
            
            # 格式化结果
            card_list = []
            for c in cards:
                card_list.append({
                    'id': c[0],
                    'type': c[1],
                    'saving_id': c[2],
                    'open_date': c[3].strftime('%Y-%m-%d') if c[3] else '',
                    'open_money': float(c[4]) if c[4] else 0.0,
                    'balance': float(c[5]) if c[5] else 0.0,
                    'password': c[6],
                    'loss': c[7] == '是',
                    'customer_id': c[8],
                    'customer_name': c[9]
                })
                
            return render_template('card_management.html', 
                                  cards=card_list,
                                  search_term=search_term,
                                  search_type=search_type)
    finally:
        conn.close()

@app.route('/admin/cards/add', methods=['GET', 'POST'])
@login_required(role='admin')
def add_card():
    """添加新银行卡"""
    if request.method == 'POST':
        card_id = request.form['card_id']
        card_type = request.form['card_type']
        saving_id = request.form['saving_id'] or None
        open_date = request.form['open_date'] or None
        open_money = float(request.form['open_money'])
        balance = float(request.form['balance'])
        password = request.form['password']
        loss = '是' if request.form.get('loss') else '否'
        customer_id = int(request.form['customer_id'])
        
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                cursor.execute(
                    "INSERT INTO cardInfo (cardID, curID, savingID, openDate, "
                    "openMoney, balance, pass, ISReportLoss, customerID) "
                    "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                    (card_id, card_type, saving_id, open_date, open_money, 
                     balance, password, loss, customer_id)
                )
                conn.commit()
                flash('银行卡添加成功', 'success')
                return redirect(url_for('manage_cards'))
        except Exception as e:
            conn.rollback()
            flash(f'添加银行卡失败: {str(e)}', 'danger')
        finally:
            conn.close()
    
    # 获取客户列表用于下拉选择
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            return render_template('add_card.html', customers=customers)
    finally:
        conn.close()

@app.route('/admin/cards/edit/<string:card_id>', methods=['GET', 'POST'])
@login_required(role='admin')
def edit_card(card_id):
    """编辑银行卡信息"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                card_type = request.form['card_type']
                saving_id = request.form['saving_id'] or None
                open_date = request.form['open_date'] or None
                open_money = float(request.form['open_money'])
                balance = float(request.form['balance'])
                password = request.form['password']
                loss = '是' if request.form.get('loss') else '否'
                customer_id = int(request.form['customer_id'])
                
                cursor.execute(
                    "UPDATE cardInfo SET curID = %s, savingID = %s, openDate = %s, "
                    "openMoney = %s, balance = %s, pass = %s, ISReportLoss = %s, "
                    "customerID = %s WHERE cardID = %s",
                    (card_type, saving_id, open_date, open_money, balance, 
                     password, loss, customer_id, card_id)
                )
                conn.commit()
                flash('银行卡信息更新成功', 'success')
                return redirect(url_for('manage_cards'))
            
            # 获取当前银行卡信息
            cursor.execute(
                "SELECT curID, savingID, openDate, openMoney, balance, "
                "pass, ISReportLoss, customerID FROM cardInfo WHERE cardID = %s",
                (card_id,)
            )
            card = cursor.fetchone()
            
            if not card:
                flash('银行卡不存在', 'danger')
                return redirect(url_for('manage_cards'))
            
            # 获取客户列表
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            
            return render_template('edit_card.html', 
                                  card_id=card_id,
                                  card={
                                      'type': card[0],
                                      'saving_id': card[1],
                                      'open_date': card[2].strftime('%Y-%m-%d') if card[2] else '',
                                      'open_money': float(card[3]) if card[3] else 0.0,
                                      'balance': float(card[4]) if card[4] else 0.0,
                                      'password': card[5],
                                      'loss': card[6] == '是',
                                      'customer_id': card[7]
                                  },
                                  customers=customers)
    finally:
        conn.close()

@app.route('/admin/cards/delete/<string:card_id>', methods=['POST'])
@login_required(role='admin')
def delete_card(card_id):
    """删除银行卡"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 检查是否有交易记录
            cursor.execute(
                "SELECT COUNT(*) FROM tradeInfo WHERE cardID = %s",
                (card_id,)
            )
            if cursor.fetchone()[0] > 0:
                flash('无法删除银行卡，该卡有交易记录', 'danger')
                return redirect(url_for('manage_cards'))
                
            cursor.execute(
                "DELETE FROM cardInfo WHERE cardID = %s",
                (card_id,)
            )
            conn.commit()
            flash('银行卡删除成功', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'删除银行卡失败: {str(e)}', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('manage_cards'))


# ====================== 存款业务管理 ======================
@app.route('/admin/deposits')
@login_required(role='admin')
def manage_deposits():
    """存款业务管理主页面"""
    search_term = request.args.get('search', '')
    search_type = request.args.get('type', 'name')  # name, id
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 基础查询
            query = """
                SELECT savingID, savingName, descrip 
                FROM deposit
            """
            
            # 添加搜索条件
            params = []
            if search_term:
                if search_type == 'name':
                    query += " WHERE savingName LIKE %s"
                    params.append(f"%{search_term}%")
                elif search_type == 'id':
                    query += " WHERE savingID = %s"
                    params.append(int(search_term))
            
            query += " ORDER BY savingID"
            cursor.execute(query, params)
            deposits = cursor.fetchall()
            
            # 格式化结果
            deposit_list = []
            for d in deposits:
                deposit_list.append({
                    'id': d[0],
                    'name': d[1],
                    'description': d[2] or ''
                })
                
            return render_template('deposit_management.html', 
                                  deposits=deposit_list,
                                  search_term=search_term,
                                  search_type=search_type)
    finally:
        conn.close()

@app.route('/admin/deposits/add', methods=['GET', 'POST'])
@login_required(role='admin')
def add_deposit():
    """添加新存款业务"""
    if request.method == 'POST':
        name = request.form['name']
        description = request.form['description'] or None
        
        conn = pymysql.connect(**db_config)
        try:
            with conn.cursor() as cursor:
                cursor.execute(
                    "INSERT INTO deposit (savingName, descrip) "
                    "VALUES (%s, %s)",
                    (name, description)
                )
                conn.commit()
                flash('存款业务添加成功', 'success')
                return redirect(url_for('manage_deposits'))
        except Exception as e:
            conn.rollback()
            flash(f'添加存款业务失败: {str(e)}', 'danger')
        finally:
            conn.close()
    
    return render_template('add_deposit.html')

@app.route('/admin/deposits/edit/<int:deposit_id>', methods=['GET', 'POST'])
@login_required(role='admin')
def edit_deposit(deposit_id):
    """编辑存款业务信息"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            if request.method == 'POST':
                name = request.form['name']
                description = request.form['description'] or None
                
                cursor.execute(
                    "UPDATE deposit SET savingName = %s, descrip = %s "
                    "WHERE savingID = %s",
                    (name, description, deposit_id)
                )
                conn.commit()
                flash('存款业务信息更新成功', 'success')
                return redirect(url_for('manage_deposits'))
            
            # 获取当前存款业务信息
            cursor.execute(
                "SELECT savingName, descrip FROM deposit WHERE savingID = %s",
                (deposit_id,)
            )
            deposit = cursor.fetchone()
            
            if not deposit:
                flash('存款业务不存在', 'danger')
                return redirect(url_for('manage_deposits'))
                
            return render_template('edit_deposit.html', 
                                  deposit_id=deposit_id,
                                  deposit={
                                      'name': deposit[0],
                                      'description': deposit[1] or ''
                                  })
    finally:
        conn.close()

@app.route('/admin/deposits/delete/<int:deposit_id>', methods=['POST'])
@login_required(role='admin')
def delete_deposit(deposit_id):
    """删除存款业务"""
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 检查是否有关联的银行卡
            cursor.execute(
                "SELECT COUNT(*) FROM cardInfo WHERE savingID = %s",
                (deposit_id,)
            )
            if cursor.fetchone()[0] > 0:
                flash('无法删除存款业务，该业务有关联的银行卡', 'danger')
                return redirect(url_for('manage_deposits'))
                
            cursor.execute(
                "DELETE FROM deposit WHERE savingID = %s",
                (deposit_id,)
            )
            conn.commit()
            flash('存款业务删除成功', 'success')
    except Exception as e:
        conn.rollback()
        flash(f'删除存款业务失败: {str(e)}', 'danger')
    finally:
        conn.close()
    # ====================== 交易管理 ======================





@app.route('/admin/transactions')
@login_required(role='admin')
def manage_transactions():
    """交易管理主页面"""
    # 获取查询参数
    card_id = request.args.get('card_id', '')
    customer_id = request.args.get('customer_id', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    trade_type = request.args.get('type', 'all')
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 基础查询
            query = """
                SELECT t.tradeID, t.tradeDate, t.tradeType, t.tradeMoney, t.remark,
                       t.cardID, u.customerName
                FROM tradeInfo t
                JOIN cardInfo c ON t.cardID = c.cardID
                JOIN userInfo u ON c.customerID = u.customerID
                WHERE 1=1
            """
            params = []
            
            # 添加过滤条件
            if card_id:
                query += " AND t.cardID LIKE %s"
                params.append(f"%{card_id}%")
            if customer_id:
                query += " AND u.customerID = %s"
                params.append(customer_id)
            if start_date:
                query += " AND t.tradeDate >= %s"
                params.append(start_date)
            if end_date:
                query += " AND t.tradeDate <= %s"
                params.append(end_date)
            if trade_type != 'all':
                query += " AND t.tradeType = %s"
                params.append(trade_type)
                
            query += " ORDER BY t.tradeDate DESC"
            cursor.execute(query, params)
            transactions = cursor.fetchall()
            
            # 格式化结果
            transaction_list = []
            for t in transactions:
                transaction_list.append({
                    'id': t[0],
                    'date': t[1].strftime('%Y-%m-%d %H:%M'),
                    'type': t[2],
                    'amount': float(t[3]),
                    'remark': t[4],
                    'card_id': t[5],
                    'customer_name': t[6]
                })
                
            # 获取客户列表用于过滤
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            
            return render_template('transaction_management.html', 
                                  transactions=transaction_list,
                                  customers=customers,
                                  search_params={
                                      'card_id': card_id,
                                      'customer_id': customer_id,
                                      'start_date': start_date,
                                      'end_date': end_date,
                                      'trade_type': trade_type
                                  })
    finally:
        conn.close()

@app.route('/admin/transactions/export')
@login_required(role='admin')
def export_transactions():
    """导出交易记录为CSV"""
    # 获取与交易管理页面相同的查询条件
    card_id = request.args.get('card_id', '')
    customer_id = request.args.get('customer_id', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    trade_type = request.args.get('type', 'all')
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            query = """
                SELECT t.tradeDate, t.tradeType, t.tradeMoney, t.remark,
                       t.cardID, u.customerName, u.customerID
                FROM tradeInfo t
                JOIN cardInfo c ON t.cardID = c.cardID
                JOIN userInfo u ON c.customerID = u.customerID
                WHERE 1=1
            """
            params = []
            
            if card_id:
                query += " AND t.cardID LIKE %s"
                params.append(f"%{card_id}%")
            if customer_id:
                query += " AND u.customerID = %s"
                params.append(customer_id)
            if start_date:
                query += " AND t.tradeDate >= %s"
                params.append(start_date)
            if end_date:
                query += " AND t.tradeDate <= %s"
                params.append(end_date)
            if trade_type != 'all':
                query += " AND t.tradeType = %s"
                params.append(trade_type)
                
            cursor.execute(query, params)
            transactions = cursor.fetchall()
            
            # 创建CSV输出
            output = io.BytesIO()
            wrapper = io.TextIOWrapper(
                output,
                encoding='utf-8-sig'  # 添加BOM标记，使Excel正确识别编码
            )
            
            writer = csv.writer(wrapper)
            writer.writerow(['交易时间', '交易类型', '交易金额', '备注', '银行卡号', '客户姓名', '客户ID'])
            
            for t in transactions:
                card_id = "\t" + t[4]
                writer.writerow([
                    t[0].strftime('%Y-%m-%d %H:%M'),
                    t[1],
                    t[2],
                    t[3],
                    card_id,
                    t[5],
                    t[6]
                ])
            # 确保所有数据写入
            wrapper.flush()
            # 重置指针位置
            output.seek(0)
            # 关闭文本包装器但保留底层字节流
            wrapper.detach()
            
            return send_file(
                output,
                as_attachment=True,
                download_name='transactions_export.csv',
                mimetype='text/csv'
            )
    finally:
        conn.close()
    # ====================== 报表统计 ======================
@app.route('/admin/reports')
@login_required(role='admin')
def reports_statistics():
    """报表统计主页面"""
    # 获取时间范围参数（默认最近30天）
    start_date = request.args.get('start_date', (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d'))
    end_date = request.args.get('end_date', datetime.now().strftime('%Y-%m-%d'))
    
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            # 1. 交易类型统计
            cursor.execute("""
                SELECT tradeType, COUNT(*) as count, SUM(tradeMoney) as total
                FROM tradeInfo
                WHERE tradeDate BETWEEN %s AND %s
                GROUP BY tradeType
            """, (start_date, end_date))
            trade_type_stats = cursor.fetchall()
            
            # 2. 每日交易趋势
            cursor.execute("""
                SELECT DATE(tradeDate) as day, 
                       COUNT(*) as transaction_count,
                       SUM(CASE WHEN tradeType = '存款' THEN tradeMoney ELSE 0 END) as deposit_total,
                       SUM(CASE WHEN tradeType = '取款' THEN tradeMoney ELSE 0 END) as withdraw_total
                FROM tradeInfo
                WHERE tradeDate BETWEEN %s AND %s
                GROUP BY day
                ORDER BY day
            """, (start_date, end_date))
            daily_trends = cursor.fetchall()
            
            # 3. 客户交易排名
            cursor.execute("""
                SELECT u.customerID, u.customerName, 
                       COUNT(t.tradeID) as transaction_count,
                       SUM(t.tradeMoney) as total_amount
                FROM tradeInfo t
                JOIN cardInfo c ON t.cardID = c.cardID
                JOIN userInfo u ON c.customerID = u.customerID
                WHERE t.tradeDate BETWEEN %s AND %s
                GROUP BY u.customerID
                ORDER BY total_amount DESC
                LIMIT 10
            """, (start_date, end_date))
            customer_ranking = cursor.fetchall()
            
            # 4. 存款类型分布
            cursor.execute("""
                SELECT d.savingName, COUNT(*) as account_count,
                       SUM(c.balance) as total_balance
                FROM cardInfo c
                JOIN deposit d ON c.savingID = d.savingID
                GROUP BY d.savingName
            """)
            deposit_distribution = cursor.fetchall()
            
            return render_template('reports_statistics.html',
                                   trade_type_stats=trade_type_stats,
                                   daily_trends=daily_trends,
                                   customer_ranking=customer_ranking,
                                   deposit_distribution=deposit_distribution,
                                   start_date=start_date,
                                   end_date=end_date)
    finally:
        conn.close()
    # 添加Jinja2过滤器
@app.template_filter('format_currency')
def format_currency(value):
    try:
        return "{:,.2f}".format(float(value))
    except (ValueError, TypeError):
        return value
    return redirect(url_for('manage_deposits'))
if __name__ == '__main__':
    app.run(debug=True, port=5000)